﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SQLite;
using System.IO;
using CSV;
using Microsoft.VisualBasic;
using System.Runtime.InteropServices;
using System.Globalization;
using System.Collections;
using System.Threading;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        const double workingday = 9.5;
        protected string FileName;
        protected bool Modified;
        private const int MaxColumns = 3;
        string log = "";
        int count = 0;
        bool warnimg = false;
        string pwarnmsg = "";
        string firstupdate = "";
        string lastupdate = "";
        int rowcount = 0;
        //   ArrayList peopledone = new ArrayList();
        List<string> peopledone = new List<string>();
        int peoplecoun = 0;
        public Form1()
        {
            InitializeComponent();
            InitializeGrid();
            InitializeUpdate();
            string query = "SELECT workerid FROM users WHERE working = 1 ORDER BY workerid ASC";
            FillDropDownList(query, cbxENumber);
            FillDropDownList(query, comboBox1);
            string querya = "SELECT workerid FROM users ORDER By workerid ASC";
            FillDropDownList(querya, cbxEAdmin);
            string query2 = "SELECT workerid FROM Users where working = 0 ORDER By workerid ASC";
            FillDropDownList(query2, cbxRein);
            FillDropDownList(querya, cbxReport);
            cbxType.SelectedIndex = 0;
            ReadData(ref firstupdate, ref lastupdate);
            setMaxmin();
        }

        private void ReadData(ref string FirstUpdate, ref string LastUpdate)
        {
            StreamReader read = new StreamReader("data.dat");
            FirstUpdate = read.ReadLine();
            LastUpdate = read.ReadLine();
            read.Close();
        }

        private void WriteData(ref string FirstUpdate, ref string LastUpdate)
        {
            StreamWriter write = new StreamWriter("data.dat");
            write.WriteLine(FirstUpdate);
            write.WriteLine(LastUpdate);
            write.Close();
        }

        private void picwarn_MouseDown(object sender, MouseEventArgs e)
        {
            if ((e.Button & MouseButtons.Left) == MouseButtons.Left)
            {
                var imagePos = e.Location; // that's it
                timerpic.Enabled = false;
                picwarn.Visible = true;
                MessageBox.Show(pwarnmsg);
                picwarn.Visible = false;

            }
        }
        public static void FillDropDownList(string Query, ComboBox DropDownName)
        {
            DropDownName.Items.Clear();
            SQLiteDataReader dr;
            SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
            con.Open();

            //Check whether the Drop Down has existing items. If YES, empty it.
            if (DropDownName.Items.Count > 0)
                DropDownName.Items.Clear();
            SQLiteCommand cmd = new SQLiteCommand(Query, con);
            dr = cmd.ExecuteReader();
            while (dr.Read())
                DropDownName.Items.Add(dr[0].ToString());
            dr.Close();
            con.Close();
        }
        private void InitializeUpdate()
        {
            lblMon.Visible = false;
            lblTue.Visible = false;
            lblWed.Visible = false;
            lblThur.Visible = false;
            lblFri.Visible = false;
            cbxMon.Visible = false;
            cbxTue.Visible = false;
            cbxWed.Visible = false;
            cbxThur.Visible = false;
            cbxFri.Visible = false;
            btnUpdateE.Visible = false;
            cbxMon.SelectedIndex = 0;
            cbxTue.SelectedIndex = 0;
            cbxWed.SelectedIndex = 0;
            cbxThur.SelectedIndex = 0;
            cbxFri.SelectedIndex = 0;

        }

        private void ButtonOnclickUpdate()
        {
            lblMon.Visible = true;
            lblTue.Visible = true;
            lblWed.Visible = true;
            lblThur.Visible = true;
            lblFri.Visible = true;
            cbxMon.Visible = true;
            cbxTue.Visible = true;
            cbxWed.Visible = true;
            cbxThur.Visible = true;
            cbxFri.Visible = true;
            btnUpdateE.Visible = true;
        }

        private void exitToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }

        private void btnTest_Click(object sender, EventArgs e)
        {
            SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
            con.Open();
            string sql = "SELECT * From users ORDER By workerid ASC";
            SQLiteCommand command = new SQLiteCommand(sql, con);
            SQLiteDataAdapter da = new SQLiteDataAdapter(command);
            DataSet ds = new DataSet();
            da.Fill(ds);
            DataTable dt = ds.Tables[0];
            this.grid.DataSource = dt;
            con.Close();
        }

        private void btnReset_Click(object sender, EventArgs e)
        {
            edtWCode.Text = "";
            edtWName.Text = "";
            edtWSurname.Text = "";
            cbxCWork.Text = "Yes";
            cbxStatus.Text = "Staff";
        }

        private void btnAddUer_Click(object sender, EventArgs e)
        {
            try
            {
                SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
                con.Open();
                int wwork = 0;
                int wstatus = 0;
                string wcode = edtWCode.Text;
                string wname = edtWName.Text;
                string wsname = edtWSurname.Text;
                if (this.cbxCWork.Items[this.cbxCWork.SelectedIndex].ToString() == "Yes")
                    wwork = 1;
                if (this.cbxStatus.Items[this.cbxStatus.SelectedIndex].ToString() == "Staff")
                    wstatus = 1;
                SQLiteCommand cmd13 = new SQLiteCommand(con);
                cmd13.CommandType = CommandType.Text;
                cmd13.CommandText = "INSERT into yearly(workerid) VALUES(@wwcode)";
                cmd13.Parameters.Add(new SQLiteParameter("@wwcode", wcode));
                cmd13.ExecuteNonQuery();
                SQLiteCommand command = new SQLiteCommand(con);
                command.CommandText = "INSERT into users(workerid,name,surname,status,working) VALUES(@wcode,@wname,@wsname,@status,@working)";
                command.CommandType = CommandType.Text;
                command.Parameters.Add(new SQLiteParameter("@wcode", wcode));
                command.Parameters.Add(new SQLiteParameter("@wname", wname));
                command.Parameters.Add(new SQLiteParameter("@wsname", wsname));
                command.Parameters.Add(new SQLiteParameter("@status", wstatus));
                command.Parameters.Add(new SQLiteParameter("@working", wwork));
                command.ExecuteNonQuery();
                con.Close();
            }
            catch (Exception ex)
            {

                MessageBox.Show("Cannot add employee. Please try again.");
            }
        }

        private void addUsersToolStripMenuItem_Click(object sender, EventArgs e)
        {
            tabControl1.SelectTab(tabOne);
        }

        private void button2_Click(object sender, EventArgs e)
        {
            SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
            con.Open();
            string sql = "SELECT * From users ORDER By workerid ASC";
            SQLiteCommand command = new SQLiteCommand(sql, con);
            SQLiteDataAdapter da = new SQLiteDataAdapter(command);
            DataSet ds = new DataSet();
            da.Fill(ds);
            DataTable dt = ds.Tables[0];
            this.grid2.DataSource = dt;
            try
            {
                grid2.Columns.Remove("Column1");
                grid2.Columns.Remove("Column2");
                grid2.Columns.Remove("Column3");
            }
            catch (Exception ex)
            {
            }
            con.Close();
        }
        private bool checkLeap(int year)
        {
            bool isleap = false;
            if (year % 400 == 0)
                isleap = true;
            else
                if (year % 100 == 0)
                    isleap = false;
                else if (year % 4 == 0)
                    isleap = true;
                else
                    isleap = false;
            return isleap;
        }
        private void InitializeGrid()
        {
            for (int i = 1; i <= MaxColumns; i++)
            {
                grid2.Columns.Add(
                    String.Format("Column{0}", i),
                    String.Format("Column{0}", i));
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {

            pbxGreen.Visible = false;
            OpenFileDialog file = new OpenFileDialog();
            file.Filter = "All files (*.*)|*.*|All files (*.*)|*.*";
            file.Title = "Select a CSV";
            if (file.ShowDialog(this) == DialogResult.OK)
            {
                ReadFile(file.FileName);
                pbxGreen.Visible = true;
            }
            textBox1.Text = file.FileName;
            string a, b, c;
            SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
            con.Open();
            progressBar1.Visible = true;
            timer1.Enabled = true;
            grid2.Columns[0].HeaderText = "Employee Number";
            grid2.Columns[1].HeaderText = "Employee Surname";
            grid2.Columns[2].HeaderText = "Employee Name";
            try
            {
                for (int rows = 0; rows < grid2.Rows.Count - 1; rows++)
                {
                    SQLiteCommand command = new SQLiteCommand(con);
                    a = grid2.Rows[rows].Cells[0].Value.ToString();
                    b = grid2.Rows[rows].Cells[1].Value.ToString();
                    c = grid2.Rows[rows].Cells[2].Value.ToString();
                    command.CommandText = "INSERT into users(workerid,name,surname) VALUES(@wcode,@wname,@wsname)";
                    SQLiteCommand cmd1 = new SQLiteCommand(con);
                    cmd1.CommandText = "INSERT into yearly(workerid) VALUES(@wid)";
                    cmd1.CommandType = CommandType.Text;
                    cmd1.Parameters.Add(new SQLiteParameter("@wid", a));
                    cmd1.ExecuteNonQuery();
                    command.CommandType = CommandType.Text;
                    command.Parameters.Add(new SQLiteParameter("@wcode", a));
                    command.Parameters.Add(new SQLiteParameter("@wname", c));
                    command.Parameters.Add(new SQLiteParameter("@wsname", b));

                    command.ExecuteNonQuery();

                }

                con.Close();
            }
            catch (Exception ex)
            {
                pbxRed.Visible = true;
            }
        }

        private bool ReadFile(string filename)
        {
            Cursor = Cursors.WaitCursor;

            try
            {
                grid2.Rows.Clear();
                List<string> columns = new List<string>();
                using (var reader = new CsvFileReader(filename))
                {
                    while (reader.ReadRow(columns))
                    {
                        grid2.Rows.Add(columns.ToArray());

                    }
                }
                FileName = filename;
                Modified = false;

                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(String.Format("Error reading from {0}.\r\n\r\n{1}", filename, ex.Message));
            }
            finally
            {
                Cursor = Cursors.Default;
            }
            return false;
        }

        private void ReadValues()
        {
            List<string> columns = new List<string>();
            using (var reader = new CsvFileReader("ReadTest.csv"))
            {
                while (reader.ReadRow(columns))
                {
                    // TODO: Do something with columns' values
                }
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void importToolStripMenuItem_Click(object sender, EventArgs e)
        {
            tabControl1.SelectTab(tabImport);
        }

        private void exportToolStripMenuItem_Click(object sender, EventArgs e)
        {


        }

        private int GetMaxColumnUsed1()
        {
            int maxColumnUsed = 0;
            foreach (DataGridViewRow row in grR.Rows)
            {
                if (!row.IsNewRow)
                {
                    for (int col = row.Cells.Count - 1; col >= 0; col--)
                    {
                        if (row.Cells[col].Value != null)
                        {
                            if (maxColumnUsed < (col + 1))
                                maxColumnUsed = (col + 1);
                            continue;
                        }
                    }
                }
            }
            return maxColumnUsed;
        }

        private int GetMaxColumnUsed()
        {
            int maxColumnUsed = 0;
            foreach (DataGridViewRow row in grid3.Rows)
            {
                if (!row.IsNewRow)
                {
                    for (int col = row.Cells.Count - 1; col >= 0; col--)
                    {
                        if (row.Cells[col].Value != null)
                        {
                            if (maxColumnUsed < (col + 1))
                                maxColumnUsed = (col + 1);
                            continue;
                        }
                    }
                }
            }
            return maxColumnUsed;
        }

        private bool WriteFile1(string filename)
        {
            Cursor = Cursors.WaitCursor;
            try
            {
                // Like Excel, we'll get the highest column number used,
                // and then write out that many columns for every row
                int numColumns = GetMaxColumnUsed1();
                using (var writer = new CsvFileWriter(filename))
                {

                    List<string> columnz = new List<string>();
                    columnz.Add("POSITION");
                    columnz.Add("EMPLOYEE NUMBER");
                    columnz.Add("TOTAL ABSENT");                   
                    for (int z = 3; z <= numColumns - 1; z++)
                    {
                        columnz.Add(grR.Columns[z].HeaderText.ToString());
                    }
                    writer.WriteRow(columnz);
                    foreach (DataGridViewRow row in grR.Rows)
                    {
                        if (!row.IsNewRow)
                        {
                            List<string> columns = new List<string>();
                            for (int col = 0; col < numColumns; col++)
                                columns.Add((string)row.Cells[col].Value.ToString());
                            writer.WriteRow(columns);
                        }
                    }
                }
                FileName = filename;
                Modified = false;
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(String.Format("Error writing to {0}.\r\n\r\n{1}", filename, ex.Message));
            }
            finally
            {
                Cursor = Cursors.Default;
            }
            return false;
        }

        private bool WriteFile(string filename)
        {
            Cursor = Cursors.WaitCursor;
            try
            {
                // Like Excel, we'll get the highest column number used,
                // and then write out that many columns for every row
                int numColumns = GetMaxColumnUsed();
                using (var writer = new CsvFileWriter(filename))
                {

                    List<string> columnz = new List<string>();
                    columnz.Add("NAME");
                    columnz.Add("SURNAME");
                    columnz.Add("WORKER ID");
                    for (int z = 3; z <= numColumns - 1; z++)
                    {
                        columnz.Add(grid3.Columns[z].HeaderText.ToString());
                    }
                    writer.WriteRow(columnz);
                    foreach (DataGridViewRow row in grid3.Rows)
                    {
                        if (!row.IsNewRow)
                        {
                            List<string> columns = new List<string>();
                            for (int col = 0; col < numColumns; col++)
                                columns.Add((string)row.Cells[col].Value.ToString());
                            writer.WriteRow(columns);
                        }
                    }
                }
                FileName = filename;
                Modified = false;
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(String.Format("Error writing to {0}.\r\n\r\n{1}", filename, ex.Message));
            }
            finally
            {
                Cursor = Cursors.Default;
            }
            return false;
        }
        private double getHours(string workerid)
        {
            string query = "SELECT totalhours FROM users WHERE workerid = '" + workerid + "'";
            SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
            con.Open();
            double h = 0;
            SQLiteCommand cmd = new SQLiteCommand(query, con);
            SQLiteDataReader myReader = cmd.ExecuteReader();
            while (myReader.Read())
            {
                h = double.Parse(myReader[0].ToString());
                // or alternatively:
                // string fname2 = myReader["FirstName"];
                // Either of these should work
            }
            return h;
        }
        private void getLeave(string workerid, Label sick, Label paid, Label fam, Label mon, Label fri)
        {
            /* SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
             con.Open();
             SQLiteCommand monc = new SQLiteCommand(con);
             monc.CommandText = "";
             monc.ExecuteNonQuery();
             return 5;*/
            int retval = 0;

            string retval1 = "";
            int famresp = 0;
            string famrespon1 = "";
            int paidleave = 0;
            string paidleave1 = "";
            int frileave = 0;
            string frileave1 = "";
            int monleave = 0;
            string monleave1 = "";
            SQLiteDataReader dr;
            SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
            con.Open();
            string query = "SELECT totalsickleave FROM users WHERE workerid = '" + workerid + "'";
            string query2 = "SELECT totalfamrespon FROM users WHERE workerid = '" + workerid + "'";
            string query3 = "SELECT totalpaidleave FROM users WHERE workerid = '" + workerid + "'";
            string query4 = "SELECT totalmonday FROM users WHERE workerid = '" + workerid + "'";
            string query5 = "SELECT totalfriday FROM users WHERE workerid = '" + workerid + "'";
            SQLiteCommand cmd = new SQLiteCommand(query, con);
            SQLiteCommand cmd1 = new SQLiteCommand(query2, con);
            SQLiteCommand cmd2 = new SQLiteCommand(query3, con);
            SQLiteCommand cmd3 = new SQLiteCommand(query4, con);
            SQLiteCommand cmd4 = new SQLiteCommand(query5, con);
            //Reading totalsickleave
            SQLiteDataReader myReader = cmd.ExecuteReader();
            // Run through the results
            while (myReader.Read())
            {
                retval1 = myReader[0].ToString(); ;
                // or alternatively:
                // string fname2 = myReader["FirstName"];
                // Either of these should work
            }
            retval = int.Parse(retval1);
            myReader.Close();
            //End SickLeave Reader

            //Reading total fam responsibility
            SQLiteDataReader famreader = cmd1.ExecuteReader();
            // Run through the results
            while (famreader.Read())
            {
                famrespon1 = famreader[0].ToString();
                // or alternatively:
                // string fname2 = myReader["FirstName"];
                // Either of these should work
            }
            famresp = int.Parse(famrespon1);
            famreader.Close();
            //End fam respon Reader

            //Reading total paid leave
            SQLiteDataReader paidreader = cmd2.ExecuteReader();
            // Run through the results
            while (paidreader.Read())
            {
                paidleave1 = paidreader[0].ToString();
                // or alternatively:
                // string fname2 = myReader["FirstName"];
                // Either of these should work
            }
            paidleave = int.Parse(paidleave1);
            paidreader.Close();
            //End paid leave reader

            //Reading total friday leave
            SQLiteDataReader monreader = cmd3.ExecuteReader();
            // Run through the results
            while (monreader.Read())
            {
                monleave1 = monreader[0].ToString();
                // or alternatively:
                // string fname2 = myReader["FirstName"];
                // Either of these should work
            }
            monleave = int.Parse(monleave1);
            monreader.Close();
            //End friday leave reader

            //Reading total monday leave
            SQLiteDataReader frireader = cmd4.ExecuteReader();
            // Run through the results
            while (frireader.Read())
            {
                frileave1 = frireader[0].ToString();
                // or alternatively:
                // string fname2 = myReader["FirstName"];
                // Either of these should work
            }
            frileave = int.Parse(frileave1);
            frireader.Close();
            //End monday leave reader
            con.Close();
            sick.Text = "Total Sick Leave: " + retval + "/30";
            fam.Text = "Total Family Respon Leave: " + famresp + "/3";
            paid.Text = "Total Paid Leave: " + paidleave + "/15";
            fri.Text = "Total Fridays Absent: " + frileave;
            mon.Text = "Total Mondays Absent: " + monleave;

            if (paidleave == 15)
            {
                btnUpdateE.Enabled = true;
                MessageBox.Show("Paid Leave Full");
            }
            if (famresp == 3)
            {
                btnUpdateE.Enabled = true;
                MessageBox.Show("Fam Resp Full");
            }
            if (retval == 30)
            {
                btnUpdateE.Enabled = true;
                MessageBox.Show("Sick Leave Full");
            }

            if ((frileave > 3) && (monleave > 3))
            {

                pwarnmsg = "More than 3 days absent on both a Friday and Monday";
                startTimerWarn();
            }
            else
                if (frileave > 3)
                {

                    pwarnmsg = "Absent for more than 3 Fridays";
                    startTimerWarn();
                }
                else
                    if (monleave > 3)
                    {
                        pwarnmsg = "Absent for more than 3 Mondays";
                        startTimerWarn();
                    }


        }
        private void startTimerWarn()
        {
            lblWarn.Text = pwarnmsg;
            picwarn.Visible = true;
            timerpic.Enabled = true;
        }
        private string warnMessage(string message)
        {
            return message;
        }
        private void button3_Click(object sender, EventArgs e)
        {
            int day = int.Parse(dtmUpdate.Value.Day.ToString());
            int month = int.Parse(dtmUpdate.Value.Month.ToString());
            int year = int.Parse(dtmUpdate.Value.Year.ToString());
            peopledone.Clear();
            bool leap = checkLeap(year);
            int days = checkDays(month, leap);
            int[] dayarr = new int[5];
            int[] montharr = new int[5];
            int[] yeararr = new int[5];
            int dcounter = 0;
            if ((day + 5) > days)
            {
                for (int x = day; x <= days; x++)
                {
                    dayarr[dcounter] = x;
                    montharr[dcounter] = month;
                    yeararr[dcounter] = year;
                    dcounter++;
                }
                for (int y = 1; y <= (4 - (days - day)); y++)
                {
                    dayarr[dcounter] = y;
                    if (month == 12)
                    {
                        montharr[dcounter] = 1;
                        yeararr[dcounter] = year + 1;
                    }
                    else
                    {
                        yeararr[dcounter] = year;
                        montharr[dcounter] = month + 1;
                    }
                    dcounter++;
                }
            }
            else
            {
                for (int x = day; x <= day + 4; x++)
                {
                    dayarr[dcounter] = x;
                    montharr[dcounter] = month;
                    yeararr[dcounter] = year;
                    dcounter++;
                }
            }
            string cool = "";
            /*  for (int z = 0; z <= 6; z++)
              {
                  cool = cool + montharr[z] + "\n";
              }
              MessageBox.Show(cool);
             */
            //test
            if (firstupdate == "")
                firstupdate = "01/01/2000";
            DateTime first = DateTime.ParseExact(firstupdate, "dd/MM/yyyy", null);
            string firstup = first.ToString("dd/MM/yyyy");
            if (lastupdate == "")
                lastupdate = "02/01/2000";
            DateTime last = DateTime.ParseExact(lastupdate, "dd/MM/yyyy", null);
            string lastup = last.ToString("dd/MM/yyyy");
            int result = 0;
            bool update = false;
            string comp1 = "";
            bool isfirstupdate = false;
            int isff = 0;
            string[] stringarr = new string[10];
            for (int z = 4; z >= 0; z--)
            {
                if ((dayarr[z] < 10) && (montharr[z] < 10))
                {
                    comp1 = "0" + dayarr[z].ToString() + "/0" + montharr[z].ToString() + "/" + yeararr[z].ToString();

                }
                else
                    if (dayarr[z] < 10)
                    {
                        comp1 = "0" + dayarr[z].ToString() + "/" + montharr[z].ToString() + "/" + yeararr[z].ToString();

                    }
                    else
                        if (montharr[z] < 10)
                        {
                            comp1 = dayarr[z].ToString() + "/0" + montharr[z].ToString() + "/" + yeararr[z].ToString();

                        }
                        else
                        {
                            comp1 = dayarr[z].ToString() + "/" + montharr[z].ToString() + "/" + yeararr[z].ToString();
                        }

                result = DateTime.Compare(DateTime.ParseExact(comp1, "dd/MM/yyyy", null), first);
            }
            string isf = returnproperdate(dayarr[0].ToString() + "/" + montharr[0].ToString() + "/" + yeararr[0].ToString());
            DateTime isfd = DateTime.ParseExact(isf, "dd/MM/yyyy", null);
            isff = DateTime.Compare(isfd, first);
            if (isff < 0)
                isfirstupdate = true;
            else
                if (isff == 0)
                    isfirstupdate = false;
                else
                    if (isff > 0)
                        isfirstupdate = false;
                    else isfirstupdate = false;

            if (result < 0)
            {
                update = false;

            }
            else
                if (result == 0)
                {
                    update = false;
                }
                else
                    if (result > 0)
                    {
                        for (int x = 4; x >= 0; x--)
                        {
                            if ((dayarr[x] < 10) && (montharr[x] < 10))
                                comp1 = "0" + dayarr[x].ToString() + "/0" + montharr[x].ToString() + "/" + yeararr[x].ToString();
                            else
                                if (dayarr[x] < 10)
                                    comp1 = "0" + dayarr[x].ToString() + "/" + montharr[x].ToString() + "/" + yeararr[x].ToString();
                                else
                                    if (montharr[x] < 10)
                                        comp1 = dayarr[x].ToString() + "/0" + montharr[x].ToString() + "/" + yeararr[x].ToString();
                                    else comp1 = dayarr[x].ToString() + "/" + montharr[x].ToString() + "/" + yeararr[x].ToString();
                            result = DateTime.Compare(DateTime.ParseExact(comp1, "dd/MM/yyyy", null), last);
                        }
                    }
            if (result < 0)
            {
                update = false;

            }
            else
                if (result == 0)
                {
                    update = false;

                }
                else
                    update = true;

            //end test
            
            ButtonOnclickUpdate();
            lblMon.Text = dayarr[0].ToString() + "/" + montharr[0].ToString() + "/" + yeararr[0].ToString();
            lblTue.Text = dayarr[1].ToString() + "/" + montharr[1].ToString() + "/" + yeararr[1].ToString();
            lblWed.Text = dayarr[2].ToString() + "/" + montharr[2].ToString() + "/" + yeararr[2].ToString();
            lblThur.Text = dayarr[3].ToString() + "/" + montharr[3].ToString() + "/" + yeararr[3].ToString();
            lblFri.Text = dayarr[4].ToString() + "/" + montharr[4].ToString() + "/" + yeararr[4].ToString();
            //comparing date
            DateTime datemon = new DateTime(yeararr[0], montharr[0], dayarr[0]);
            DateTime datefri = new DateTime(yeararr[0], montharr[0], dayarr[0]);
            //end date compare
            // if (update == true)
            //  {
            string moncol = lblMon.Text;
            string tuecol = lblTue.Text;
            string wedcol = lblWed.Text;
            string thurcol = lblThur.Text;
            string fricol = lblFri.Text;

            //start changing values for moncolumn
            string text = moncol;
            string[] bits = text.Split('/');
            if ((int.Parse(bits[0]) < 10) && (int.Parse(bits[1]) < 10) && (int.Parse(bits[2]) < 10))
                text = "0" + bits[0].ToString() + "/0" + bits[1].ToString() + "/0" + bits[2].ToString();
            else
                if ((int.Parse(bits[0]) < 10) && (int.Parse(bits[1]) < 10))
                    text = "0" + bits[0].ToString() + "/0" + bits[1].ToString() + "/" + bits[2].ToString();
                else
                    if ((int.Parse(bits[0]) < 10) && (int.Parse(bits[2]) < 10))
                        text = "0" + bits[0].ToString() + "/" + bits[1].ToString() + "/0" + bits[2].ToString();
                    else
                        if ((int.Parse(bits[1]) < 10) && (int.Parse(bits[2]) < 10))
                            text = bits[0].ToString() + "/0" + bits[1].ToString() + "/0" + bits[2].ToString();
                        else
                            if (int.Parse(bits[0]) < 10)
                                text = "0" + bits[0].ToString() + "/" + bits[1].ToString() + "/" + bits[2].ToString();
                            else
                                if (int.Parse(bits[1]) < 10)
                                    text = bits[0].ToString() + "/0" + bits[1].ToString() + "/" + bits[2].ToString();
                                else
                                    if (int.Parse(bits[2]) < 10)
                                        text = bits[0].ToString() + "/" + bits[1].ToString() + "/0" + bits[2].ToString();
            //end changing values for mon column
            //////////////////////////////////////////////
            //start changing columns for sunday or last
            string text2 = fricol;
            string[] bits2 = text2.Split('/');
            if ((int.Parse(bits2[0]) < 10) && (int.Parse(bits2[1]) < 10) && (int.Parse(bits2[2]) < 10))
                text2 = "0" + bits2[0].ToString() + "/0" + bits2[1].ToString() + "/0" + bits2[2].ToString();
            else
                if ((int.Parse(bits2[0]) < 10) && (int.Parse(bits2[1]) < 10))
                    text2 = "0" + bits2[0].ToString() + "/0" + bits2[1].ToString() + "/" + bits2[2].ToString();
                else
                    if ((int.Parse(bits2[0]) < 10) && (int.Parse(bits2[2]) < 10))
                        text2 = "0" + bits2[0].ToString() + "/" + bits2[1].ToString() + "/0" + bits2[2].ToString();
                    else
                        if ((int.Parse(bits2[1]) < 10) && (int.Parse(bits2[2]) < 10))
                            text2 = bits2[0].ToString() + "/0" + bits2[1].ToString() + "/0" + bits2[2].ToString();
                        else
                            if (int.Parse(bits2[0]) < 10)
                                text2 = "0" + bits2[0].ToString() + "/" + bits2[1].ToString() + "/" + bits2[2].ToString();
                            else
                                if (int.Parse(bits2[1]) < 10)
                                    text2 = bits2[0].ToString() + "/0" + bits2[1].ToString() + "/" + bits2[2].ToString();
                                else
                                    if (int.Parse(bits2[2]) < 10)
                                        text2 = bits2[0].ToString() + "/" + bits2[1].ToString() + "/0" + bits2[2].ToString();

            //end changing columns for sunday or last
            SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
            con.Open();
            SQLiteCommand monc = new SQLiteCommand(con);
            monc.CommandText = "ALTER TABLE main.yearly ADD COLUMN '" + returnproperdate(moncol) + "' VARCHAR";
            monc.ExecuteNonQuery();
            SQLiteCommand tuec = new SQLiteCommand(con);
            tuec.CommandText = "ALTER TABLE main.yearly ADD COLUMN '" + returnproperdate(tuecol) + "' VARCHAR";
            tuec.ExecuteNonQuery();
            SQLiteCommand wedc = new SQLiteCommand(con);
            wedc.CommandText = "ALTER TABLE main.yearly ADD COLUMN '" + returnproperdate(wedcol) + "' VARCHAR";
            wedc.ExecuteNonQuery();
            SQLiteCommand thurc = new SQLiteCommand(con);
            thurc.CommandText = "ALTER TABLE main.yearly ADD COLUMN '" + returnproperdate(thurcol) + "' VARCHAR";
            thurc.ExecuteNonQuery();
            SQLiteCommand fric = new SQLiteCommand(con);
            fric.CommandText = "ALTER TABLE main.yearly ADD COLUMN '" + returnproperdate(fricol) + "' VARCHAR";
            fric.ExecuteNonQuery();
            con.Close();
            //    button3.Enabled = false;
            //   dtmUpdate.Enabled = false;

            /*  if (firstupdate != "")
              {
                  firstupdate = text;
                  lastupdate = text2;
              }*/

            if (isfirstupdate == true)
            {
                firstupdate = text;
                lastupdate = text2;
            }
            else
                lastupdate = text2;
            WriteData(ref firstupdate, ref lastupdate);
            setMaxmin();
            //  }
            // else
            // {
            // MessageBox.Show("You have previously updated");

            //  }
            //
        }  //end button click

        private string returnproperdate(string givedate1)
        {
            string t = givedate1;
            string givedate = "";
            string[] tt = t.Split('/');
            if ((int.Parse(tt[0]) < 10) && (int.Parse(tt[1]) < 10))
                givedate = "0" + tt[0].ToString() + "/0" + tt[1].ToString() + "/" + tt[2].ToString();
            else
                if (int.Parse(tt[0]) < 10)
                    givedate = "0" + tt[0].ToString() + "/" + tt[1].ToString() + "/" + tt[2].ToString();
                else
                    if (int.Parse(tt[1]) < 10)
                        givedate = tt[0].ToString() + "/0" + tt[1].ToString() + "/" + tt[2].ToString();
                    else givedate = tt[0].ToString() + "/" + tt[1].ToString() + "/" + tt[2].ToString();

            return givedate;
        }
        //coolfunc
        private int checkDays(int month, bool leap)
        {
            if (leap == false)
            {
                switch (month)
                {
                    case 1: return 31;
                    case 2: return 28;
                    case 3: return 31;
                    case 4: return 30;
                    case 5: return 31;
                    case 6: return 30;
                    case 7: return 31;
                    case 8: return 31;
                    case 9: return 30;
                    case 10: return 31;
                    case 11: return 30;
                    case 12: return 31;
                    default: return 0;
                } //end switch month

            }//end if leap is flase
            else
            {
                switch (month)
                {
                    case 1: return 31;
                    case 2: return 29;
                    case 3: return 31;
                    case 4: return 30;
                    case 5: return 31;
                    case 6: return 30;
                    case 7: return 31;
                    case 8: return 31;
                    case 9: return 30;
                    case 10: return 31;
                    case 11: return 30;
                    case 12: return 31;
                    default: return 0;
                } //end switch month
            }//end else 
        }

        private string getType(ComboBox cbx)
        {
            int iex = cbx.SelectedIndex;
            switch (iex)
            {
                case 0: return "ATTENDED";
                case 1: return "PSC";
                case 2: return "UL";
                case 3: return "PL";
                case 4: return "PFR";
                case 5: return "USC";
                case 6: return "AW";
                case 7: return "IOD";
                case 8: return "AB";
                case 9: return "NOWORK";
                case 10: return "MAT";
                default: return "ATTENDED";
            }
        }
        private void updateLog(ref string log, string mon, string tue, string wed, string thur, string fri)
        {
            log += "-=-=-=-=-=-=-=-=-=-=-\n";
            log += "Employee Number: " + cbxENumber.SelectedText + "\n";
            log += "Name: " + edtEName.Text + "\n";
            log += "Surname: " + edtESurname.Text + "\n";
            log += "Monday: " + mon + "\n";
            log += "Tuesday: " + tue + "\n";
            log += "Wednesday: " + wed + "\n";
            log += "Thursday: " + thur + "\n";
            log += "Friday: " + fri + "\n";
            log += "-=-=-=-=-=-=-=-=-=-=-\n";
        }

        private int isAnyNoWork(string mon, string tue, string wed, string thur, string fri)
        {
            int tot = 0;
            if (mon == "NOWORK")
                tot++;
            if (tue == "NOWORK")
                tot++;
            if (wed == "NOWORK")
                tot++;
            if (thur == "NOWORK")
                tot++;
            if (fri == "NOWORK")
                tot++;
            return tot;
        }
        private int isAnyAwol(string mon, string tue, string wed, string thur, string fri)
        {
            int tot = 0;
            if (mon == "AW")
                tot++;
            if (tue == "AW")
                tot++;
            if (wed == "AW")
                tot++;
            if (thur == "AW")
                tot++;
            if (fri == "AW")
                tot++;
            return tot;
        }

        private void button4_Click(object sender, EventArgs e)
        {
            int aw = 0;
            int nowork = 0;
            bool isdone = false;
            if (peopledone.Count != 0)
            {
                for (int z = 0; z <= peopledone.Count - 1; z++)
                {
                    if (peopledone[z] == cbxENumber.Text)
                        isdone = true;
                }
            }
            if (cbxENumber.Text != "")
            {
                if (isdone == false)
                {
                    if (MessageBox.Show("Are you sure you want to update this employee? Is all the information correct?", "Employee Update", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                    {
                        string mon = getType(cbxMon);
                        string tue = getType(cbxTue);
                        string wed = getType(cbxWed);
                        string thur = getType(cbxThur);
                        string fri = getType(cbxFri);
                        string workerid = cbxENumber.Text;
                        string moncol = lblMon.Text;
                        string tuecol = lblTue.Text;
                        string wedcol = lblWed.Text;
                        string thurcol = lblThur.Text;
                        string fricol = lblFri.Text;
                        aw = isAnyAwol(mon, tue, wed, thur, fri);
                        nowork = isAnyNoWork(mon, tue, wed, thur, fri);
                        int totalshifts = 0;
                        int totfri = 0;
                        int totmon = 0;
                        int totalsick = 0;
                        int totalpaid = 0;
                        int totalfamresp = 0;
                        if (mon != "ATTENDED")
                            totmon = 1;
                        if (fri != "ATTENDED")
                            totfri = 1;
                        string val = "";
                        /* START CALCULATIONS */
                        switch (mon)
                        {
                            case "PFR": totalfamresp += 1; break;
                            case "PL": totalpaid += 1; break;
                            case "PSC": totalsick += 1; break;
                            default: ; break;
                        }
                        switch (tue)
                        {
                            case "PFR": totalfamresp += 1; break;
                            case "PL": totalpaid += 1; break;
                            case "PSC": totalsick += 1; break;
                            default: ; break;
                        }
                        switch (wed)
                        {
                            case "PFR": totalfamresp += 1; break;
                            case "PL": totalpaid += 1; break;
                            case "PSC": totalsick += 1; break;
                            default: ; break;
                        }
                        switch (thur)
                        {
                            case "PFR": totalfamresp += 1; break;
                            case "PL": totalpaid += 1; break;
                            case "PSC": totalsick += 1; break;
                            default: ; break;
                        }
                        switch (fri)
                        {
                            case "PFR": totalfamresp += 1; break;
                            case "PL": totalpaid += 1; break;
                            case "PSC": totalsick += 1; break;
                            default: ; break;
                        }
                        /* END CALCULATIONS */
                        /*      if (mon == "PFR")
                                  totalfamresp += 1;
                              if (tue == "PFR")
                                  totalfamresp += 1;
                              if (wed == "PFR")
                                  totalfamresp += 1;
                              if (thur == "PFR")
                                  totalfamresp += 1;
                              if (fri == "PFR")
                                  totalfamresp += 1;
                              /*---------------------
                              if (mon =="PL")
                                  totalpaid += 1;
                              if (tue == "PL")
                                  totalpaid += 1;
                              if (wed == "PL")
                                  totalpaid += 1;
                              if (thur == "PL")
                                  totalpaid += 1;
                              if (fri == "PL")
                                  totalpaid += 1;
                              /*---------------------
                              if (mon == "PSC")
                                  totalsick += 1;
                              if (tue == "PSC")
                                  totalsick += 1;
                              if (wed == "PSC")
                                  totalsick += 1;
                              if (thur == "PSC")
                                  totalsick += 1;
                              if (fri == "PSC")
                                  totalsick += 1;
                         */
                        updateLog(ref log, mon, tue, wed, thur, fri);
                        richTextBox1.Text += log;
                        //   double 
                        //Update Worker to database
                        SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
                        con.Open();
                        SQLiteCommand cmd12 = new SQLiteCommand(con);
                        cmd12.CommandType = CommandType.Text;
                        cmd12.CommandText = "DELETE From weekly";
                        cmd12.ExecuteNonQuery();
                        SQLiteCommand command = new SQLiteCommand(con);
                        command.CommandText = "INSERT into weekly(workerid,mon,tue,wed,thur,fri) VALUES(@wid,@mon,@tue,@wed,@thur,@fri)";
                        command.CommandType = CommandType.Text;
                        command.Parameters.Add(new SQLiteParameter("@wid", workerid));
                        command.Parameters.Add(new SQLiteParameter("@mon", mon));
                        command.Parameters.Add(new SQLiteParameter("@tue", tue));
                        command.Parameters.Add(new SQLiteParameter("@wed", wed));
                        command.Parameters.Add(new SQLiteParameter("@thur", thur));
                        command.Parameters.Add(new SQLiteParameter("@fri", fri));
                        command.ExecuteNonQuery();

                        SQLiteCommand newcmd = new SQLiteCommand(con);
                        newcmd.CommandText = "UPDATE users SET totalfriday = (totalfriday + '" + totfri + "'), totalsickleave=totalsickleave + @para1, totalpaidleave=totalpaidleave + @para2, totalfamrespon= totalfamrespon + @para3 WHERE workerid = @workerid";
                        newcmd.Parameters.Add(new SQLiteParameter("@workerid", workerid));
                        newcmd.Parameters.Add(new SQLiteParameter("@para1", totalsick));
                        newcmd.Parameters.Add(new SQLiteParameter("@para2", totalpaid));
                        newcmd.Parameters.Add(new SQLiteParameter("@para3", totalfamresp));
                        newcmd.ExecuteNonQuery();
                        SQLiteCommand newcmd1 = new SQLiteCommand(con);
                        newcmd1.CommandText = "UPDATE users SET totalmonday = (totalmonday + '" + totmon + "') WHERE workerid = @workerid";
                        newcmd1.Parameters.Add(new SQLiteParameter("@workerid", workerid));
                        newcmd1.ExecuteNonQuery();

                        SQLiteCommand insertUserYearly = new SQLiteCommand(con);
                        insertUserYearly.CommandText = "UPDATE yearly SET'" + returnproperdate(moncol) + "' = @ones,'" + returnproperdate(tuecol) + "' = @twos,'" + returnproperdate(wedcol) + "'=@threes,'" + returnproperdate(thurcol) + "' = @fours,'" + returnproperdate(fricol) + "' = @fives WHERE workerid = @workerid";
                        insertUserYearly.CommandType = CommandType.Text;
                        //insertUserYearly.Parameters.Add(new SQLiteParameter("@one", moncol));
                        insertUserYearly.Parameters.Add(new SQLiteParameter("@ones", mon));
                        // insertUserYearly.Parameters.Add(new SQLiteParameter("@two", tuecol));
                        insertUserYearly.Parameters.Add(new SQLiteParameter("@twos", tue));
                        //insertUserYearly.Parameters.Add(new SQLiteParameter("@three", wedcol));
                        insertUserYearly.Parameters.Add(new SQLiteParameter("@threes", wed));
                        //insertUserYearly.Parameters.Add(new SQLiteParameter("@four", thurcol));
                        insertUserYearly.Parameters.Add(new SQLiteParameter("@fours", thur));
                        //insertUserYearly.Parameters.Add(new SQLiteParameter("@five", fricol));
                        insertUserYearly.Parameters.Add(new SQLiteParameter("@fives", fri));
                        //insertUserYearly.Parameters.Add(new SQLiteParameter("@six", satcol));
                        insertUserYearly.Parameters.Add(new SQLiteParameter("@workerid", workerid));
                        insertUserYearly.ExecuteNonQuery();
                        double totalhours = 0;
                        totalshifts = 5 - (aw + nowork);
                        totalhours = (4 * 9.5) + 7;
                        string hourlyrate = edthourlyrate.Text;
                        SQLiteCommand totshifts = new SQLiteCommand(con);
                        totshifts.CommandText = "UPDATE users SET totalshifts = totalshifts + @para1, totalhours = totalhours + @para2, hourlyrate = @para3 WHERE workerid = @wid";
                        totshifts.Parameters.Add(new SQLiteParameter("@para1", totalshifts));
                        totshifts.Parameters.Add(new SQLiteParameter("@para2", totalhours));
                        totshifts.Parameters.Add(new SQLiteParameter("@para3", hourlyrate));
                        totshifts.Parameters.Add(new SQLiteParameter("@wid", workerid));
                        totshifts.ExecuteNonQuery();
                        con.Close();
                        peopledone.Add(cbxENumber.Text);
                    }
                }
                else
                {
                    MessageBox.Show("Worker already added");
                }
            }

        }
        public static string WorkerInfo2(string Query, TextBox Texthere)
        {
            string retval = "";
            int retval2 = 0;
            SQLiteDataReader dr;
            SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
            con.Open();
            SQLiteCommand cmd = new SQLiteCommand(Query, con);
            SQLiteDataReader myReader = cmd.ExecuteReader();

            // Run through the results
            while (myReader.Read())
            {
                retval = myReader.GetValue(0).ToString(); ;
                // or alternatively:
                // string fname2 = myReader["FirstName"];
                // Either of these should work
            }

            myReader.Close();
            con.Close();
            return retval;
        }

        public static string WorkerInfo(string Query, TextBox Texthere)
        {
            string retval = "";
            SQLiteDataReader dr;
            SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
            con.Open();
            SQLiteCommand cmd = new SQLiteCommand(Query, con);
            SQLiteDataReader myReader = cmd.ExecuteReader();

            // Run through the results
            while (myReader.Read())
            {
                retval = myReader.GetString(0);
                // or alternatively:
                // string fname2 = myReader["FirstName"];
                // Either of these should work
            }

            myReader.Close();
            con.Close();
            return retval;
        }

        public static string GetItem(string Query)
        {
            string retval = "";
            SQLiteDataReader dr;
            SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
            con.Open();
            SQLiteCommand cmd = new SQLiteCommand(Query, con);
            SQLiteDataReader myReader = cmd.ExecuteReader();

            // Run through the results
            while (myReader.Read())
            {
                retval = myReader[0].ToString();
                // or alternatively:
                // string fname2 = myReader["FirstName"];
                // Either of these should work
            }

            myReader.Close();
            con.Close();
            return retval;
        }

        public static double GetRate(string Query)
        {
            string retval = "";
            SQLiteDataReader dr;
            SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
            con.Open();
            SQLiteCommand cmd = new SQLiteCommand(Query, con);
            SQLiteDataReader myReader = cmd.ExecuteReader();

            // Run through the results
            while (myReader.Read())
            {
                retval = myReader[0].ToString(); ;
                // or alternatively:
                // string fname2 = myReader["FirstName"];
                // Either of these should work
            }

            myReader.Close();
            con.Close();
            return double.Parse(retval);
        }

        public int GetShifts(string Query)
        {
            string retval = "";
            SQLiteDataReader dr;
            SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
            con.Open();
            SQLiteCommand cmd = new SQLiteCommand(Query, con);
            SQLiteDataReader myReader = cmd.ExecuteReader();

            // Run through the results
            while (myReader.Read())
            {
                retval = myReader[0].ToString(); ;
                // or alternatively:
                // string fname2 = myReader["FirstName"];
                // Either of these should work
            }

            myReader.Close();
            con.Close();
            return int.Parse(retval);
        }

        private void cbxENumber_SelectedIndexChanged(object sender, EventArgs e)
        {
            btnUpdateE.Enabled = true;
            picwarn.Visible = false;
            lblWarn.Text = "";
            timerpic.Enabled = false;
            string workerinfo = cbxENumber.Text;
            string q1 = "SELECT name from users WHERE workerid = '" + workerinfo + "'";
            string q2 = "SELECT surname from users WHERE workerid = '" + workerinfo + "'";
            edtEName.Text = WorkerInfo(q1, edtEName);
            edtESurname.Text = WorkerInfo(q2, edtESurname);
            getLeave(workerinfo, lblSickLeave, lblPaidLeave, lblFam, lblmonday, lblfriday);
            //     lblSickLeave.Text = "Total Sick Leave Left: " + getTotalSickLeave(cbxENumber.Text);
            //     lblPaidLeave.Text = "Total Paid Leave Left: " + getTotalPaidLeave(cbxENumber.Text);
            //     lblFamResp.Text = "Total Family Responsibility Left: " + getTotalFamResp(cbxENumber.Text);
        }

        private void tabUpdate_Click(object sender, EventArgs e)
        {

        }

        private void button4_Click_1(object sender, EventArgs e)
        {
            string query = "SELECT workerid FROM users";
            FillDropDownList(query, cbxENumber);
        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            progressBar1.Visible = true;
            while (progressBar1.Value < 100)
            {
                progressBar1.Value += 1;
            }
            timer1.Enabled = false;
            progressBar1.Visible = false;
        }

        private void button5_Click(object sender, EventArgs e)
        {

        }

        private void button5_Click_1(object sender, EventArgs e)
        {
            if (cbxType.SelectedIndex == 0)
            {
                SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
                con.Open();
                string sql = "SELECT users.name, users.surname,yearly.*  FROM yearly JOIN users WHERE yearly.workerid = users.workerid ORDER By workerid ASC";
                SQLiteCommand command = new SQLiteCommand(sql, con);
                SQLiteDataAdapter da = new SQLiteDataAdapter(command);
                DataSet ds = new DataSet();

                da.Fill(ds);
                DataTable dt = ds.Tables[0];
                this.grid3.DataSource = dt;
                con.Close();
            }
            else
                if (cbxType.SelectedIndex == 1)
                {
                    SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
                    con.Open();
                    string sql = "SELECT users.name, users.surname,users.totalfriday,users.totalmonday,yearly.*  FROM yearly JOIN users WHERE ((yearly.workerid = users.workerid) AND (users.working = 1)) ORDER By workerid ASC";
                    SQLiteCommand command = new SQLiteCommand(sql, con);
                    SQLiteDataAdapter da = new SQLiteDataAdapter(command);
                    DataSet ds = new DataSet();

                    da.Fill(ds);
                    DataTable dt = ds.Tables[0];
                    this.grid3.DataSource = dt;
                    con.Close();
                }
                else
                    if (cbxType.SelectedIndex == 2)
                    {
                        SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
                        con.Open();
                        string sql = "SELECT users.name, users.surname,users.terminationreason,users.totalfriday,users.totalmonday,yearly.*  FROM yearly JOIN users WHERE ((yearly.workerid = users.workerid) AND (users.working = 0)) ORDER By workerid ASC";
                        SQLiteCommand command = new SQLiteCommand(sql, con);
                        SQLiteDataAdapter da = new SQLiteDataAdapter(command);
                        DataSet ds = new DataSet();

                        da.Fill(ds);
                        DataTable dt = ds.Tables[0];
                        this.grid3.DataSource = dt;
                        con.Close();
                    }


        }

        private void administratorToolStripMenuItem_Click(object sender, EventArgs e)
        {
            string input = Microsoft.VisualBasic.Interaction.InputBox("Prompt",
                       "Enter admin password.",
                       "",
                       0,
                       0);
            if (btnGenTables.Visible == true)
            {
                btnGenTables.Visible = false;
            }
            else
                if (input == "crhafrica1")
                {
                    btnGenTables.Visible = true;
                }
        }

        private void btnGenTables_Click(object sender, EventArgs e)
        {
            peopledone.Clear();
            SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
            con.Open();
            string sql = "DROP Table yearly";
            SQLiteCommand command = new SQLiteCommand(sql, con);
            command.ExecuteNonQuery();
            string sql1 = "CREATE  TABLE main.yearly (workerid  PRIMARY KEY  NOT NULL  UNIQUE )";
            SQLiteCommand co1 = new SQLiteCommand(sql1, con);
            co1.ExecuteNonQuery();
            string sql2 = "INSERT into yearly(workerid) select workerid FROM users";
            SQLiteCommand co = new SQLiteCommand(sql2, con);
            co.ExecuteNonQuery();
            string sql3 = "UPDATE users SET totalshifts = 0";
            SQLiteCommand co2 = new SQLiteCommand(sql3, con);
            co2.ExecuteNonQuery();
            con.Close();
            string fi = "01/01/2000";
            string la = "02/01/2000";
            lastupdate = la;
            firstupdate = fi;
            WriteData(ref fi, ref la);
            button3.Enabled = true;
            dtmUpdate.Enabled = true;
            InitializeUpdate();
        }

        private void terminateEmployeeToolStripMenuItem_Click(object sender, EventArgs e)
        {
            tabControl1.SelectedTab = tabTerm;
        
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            string workerinfo = comboBox1.Text;
            string q1 = "SELECT name from users WHERE workerid = '" + workerinfo + "'";
            string q2 = "SELECT surname from users WHERE workerid = '" + workerinfo + "'";
            edtTN.Text = WorkerInfo(q1, edtTN);
            edtTS.Text = WorkerInfo(q2, edtTS);
        }

        private void button7_Click(object sender, EventArgs e)
        {
            if (textBox2.Text != "")
            {
                string q1 = "UPDATE users SET working = 0, terminationreason = '" + textBox2.Text + "' WHERE workerid = '" + comboBox1.Text + "'";
                SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
                con.Open();
                SQLiteCommand command = new SQLiteCommand(q1, con);
                command.ExecuteNonQuery();

                string sql = "SELECT users.name, users.surname,users.terminationreason  FROM users WHERE users.working = 0 ORDER By workerid ASC";
                SQLiteCommand cmd = new SQLiteCommand(sql, con);
                SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
                DataSet ds = new DataSet();

                da.Fill(ds);
                DataTable dt = ds.Tables[0];
                this.grid4.DataSource = dt;
                con.Close();
                label14.Text = "Success";
            }
            else
                label14.Text = "Please enter a reason";

        }

        private void button8_Click(object sender, EventArgs e)
        {
            SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
            con.Open();
            string sql = "SELECT users.name, users.surname,users.terminationreason  FROM users WHERE users.working = 0 ORDER By surname ASC";
            SQLiteCommand cmd = new SQLiteCommand(sql, con);
            SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
            DataSet ds = new DataSet();

            da.Fill(ds);
            DataTable dt = ds.Tables[0];
            this.grid4.DataSource = dt;
            con.Close();
        }

        private void tab_Click(object sender, EventArgs e)
        {

        }

        private void button10_Click(object sender, EventArgs e)
        {
            string query = "SELECT workerid FROM users WHERE working = 0;";
            FillDropDownList(query, cbxRein);
        }

        private void button6_Click(object sender, EventArgs e)
        {
            string query = "SELECT workerid FROM users WHERE working = 1;";
            FillDropDownList(query, comboBox1);
        }

        private void button9_Click(object sender, EventArgs e)
        {
            string q1 = "UPDATE users SET working = 1, terminationreason = '' WHERE workerid = '" + cbxRein.Text + "'";
            SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
            con.Open();
            SQLiteCommand command = new SQLiteCommand(q1, con);
            command.ExecuteNonQuery();

            string sql = "SELECT users.name, users.surname,users.terminationreason  FROM users WHERE users.working = 0 ORDER By workerid ASC";
            SQLiteCommand cmd = new SQLiteCommand(sql, con);
            SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
            DataSet ds = new DataSet();

            da.Fill(ds);
            DataTable dt = ds.Tables[0];
            this.grid4.DataSource = dt;
            con.Close();
        }

        private void cbxRein_SelectedIndexChanged(object sender, EventArgs e)
        {
            string workerinfo = cbxRein.Text;
            string q1 = "SELECT name from users WHERE workerid = '" + workerinfo + "'";
            string q2 = "SELECT surname from users WHERE workerid = '" + workerinfo + "'";
            textBox5.Text = WorkerInfo(q1, textBox5);
            textBox4.Text = WorkerInfo(q2, textBox4);
        }

        private void tabOne_Click(object sender, EventArgs e)
        {
            string query = "SELECT * from users inner join on table users where yearly.workerid = users.workerid";
        }

        private void timerpic_Tick(object sender, EventArgs e)
        {
            picwarn.Visible = false;
            lblWarn.Visible = false;
            System.Threading.Thread.Sleep(50);
            picwarn.Visible = true;
            lblWarn.Visible = true;

        }

        private void picwarn_Click(object sender, EventArgs e)
        {

        }

        private void employeeAdministratorToolStripMenuItem_Click(object sender, EventArgs e)
        {

        }

        private void button11_Click(object sender, EventArgs e)
        {
            /* string filename = saveFileDialog1.FileName;
             saveFileDialog1.FileName = FileName;*/
            if (saveFileDialog1.ShowDialog(this) == DialogResult.OK)
                WriteFile(saveFileDialog1.FileName + ".csv");
        }

        private void cbxType_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void pictureBox3_Click(object sender, EventArgs e)
        {
            tabControl1.SelectedTab = tabAdmin;
        }

        private void panel1_Paint(object sender, PaintEventArgs e)
        {

        }

        private void button13_Click(object sender, EventArgs e)
        {
            tabControl1.SelectedTab = tabAdmin;
        }

        private void button18_Click(object sender, EventArgs e)
        {
            tabControl1.SelectedTab = tabDash;

        }

        private void tabDash_Click(object sender, EventArgs e)
        {

        }

        private void button17_Click(object sender, EventArgs e)
        {
            tabControl1.SelectedTab = tabImport;
        }

        private void button20_Click(object sender, EventArgs e)
        {
            string names = edtNAdmin.Text; ;
            string surname = edtSAdmin.Text;
            string pc = edtPC.Text;
            string estatus = edtEStatus.Text;
            string termreason = edtTAdmin.Text;
            string tma = edtMAdmin.Text;
            string tfa = edtFAdmin.Text;
            string tfrl = edtFRAdmin.Text;
            string pl = edtPLAdmin.Text;
            string sl = edtSLAdmin.Text;
            string wid = cbxEAdmin.Text;
            string hourlyrate = edtHRate.Text;
            string totalshifts = edtShifts.Text;            
            try
            {
                SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
                con.Open();
                SQLiteCommand cmd = new SQLiteCommand(con);
                cmd.CommandText = "UPDATE users SET name=@name, surname=@surname, status=@pc, working=@working, terminationreason=@termreason, totalfriday=@totalfriday, totalmonday=@totalmonday,totalfamrespon=@totalfamrespon,totalpaidleave=@totalpaidleave,totalsickleave=@totalsickleave, totalshifts=@totalshifts, hourlyrate=@hourlyrate WHERE workerid = @workerid";
                cmd.Parameters.Add(new SQLiteParameter("@name", names));
                cmd.Parameters.Add(new SQLiteParameter("@surname", surname));
                cmd.Parameters.Add(new SQLiteParameter("@pc", pc));
                cmd.Parameters.Add(new SQLiteParameter("@working", estatus));
                cmd.Parameters.Add(new SQLiteParameter("@termreason", termreason));
                cmd.Parameters.Add(new SQLiteParameter("@totalfriday", tfa));
                cmd.Parameters.Add(new SQLiteParameter("@totalmonday", tma));
                cmd.Parameters.Add(new SQLiteParameter("@totalfamrespon", tfrl));
                cmd.Parameters.Add(new SQLiteParameter("@totalpaidleave", pl));
                cmd.Parameters.Add(new SQLiteParameter("@totalsickleave", sl));
                cmd.Parameters.Add(new SQLiteParameter("@workerid", wid));
                cmd.Parameters.Add(new SQLiteParameter("@totalshifts", totalshifts));
                cmd.Parameters.Add(new SQLiteParameter("@hourlyrate", hourlyrate));
                cmd.ExecuteNonQuery();
                con.Close();
                MessageBox.Show("Updated");
            }
            catch (Exception ex)
            {
                MessageBox.Show("Failed to update");
            }
        }

        private void button22_Click(object sender, EventArgs e)
        {
            SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
            con.Open();
            SQLiteCommand cmd = new SQLiteCommand(con);
            cmd.CommandText = "SELECT * From users ORDER By workerid ASC";
            cmd.ExecuteNonQuery();
            SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            DataTable dt = ds.Tables[0];
            this.gridAdmin.DataSource = dt;
            con.Close();
        }

        private void gridAdmin_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            string wid = gridAdmin.CurrentCell.Value.ToString();
            MessageBox.Show(wid);
        }

         
        private void button12_Click(object sender, EventArgs e)
        {
            tabControl1.SelectedTab = tabRep;
        }

        private void cbxEAdmin_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cbxEAdmin.Text != "")
            {
                string workerinfo = cbxEAdmin.Text;
                string q1 = "SELECT name from users WHERE workerid = '" + workerinfo + "'";
                string q2 = "SELECT surname from users WHERE workerid = '" + workerinfo + "'";
                string q3 = "SELECT status from yearly WHERE workerid = '" + workerinfo + "'";
                string q4 = "SELECT working from users WHERE workerid = '" + workerinfo + "'";
                string q5 = "SELECT terminationreason from users WHERE workerid = '" + workerinfo + "'";
                string q6 = "SELECT totalfriday from users WHERE workerid = '" + workerinfo + "'";
                string q7 = "SELECT totalmonday from users WHERE workerid = '" + workerinfo + "'";
                string q8 = "SELECT totalfamrespon from users WHERE workerid = '" + workerinfo + "'";
                string q9 = "SELECT totalpaidleave from users WHERE workerid = '" + workerinfo + "'";
                string q10 = "SELECT totalsickleave from users WHERE workerid = '" + workerinfo + "'";
                string q11 = "SELECT totalshifts from users WHERE workerid = '" + workerinfo + "'";
                string q12 = "SELECT hourlyrate from users WHERE  workerid = '" + workerinfo + "'";
                edtNAdmin.Text = WorkerInfo2(q1, edtNAdmin);
                edtSAdmin.Text = WorkerInfo2(q2, edtSAdmin);
                edtTAdmin.Text = WorkerInfo2(q5, edtTAdmin);
                edtMAdmin.Text = WorkerInfo2(q7, edtMAdmin);
                edtFAdmin.Text = WorkerInfo2(q6, edtFAdmin);
                edtFRAdmin.Text = WorkerInfo2(q8, edtFRAdmin);
                edtSLAdmin.Text = WorkerInfo2(q10, edtSLAdmin);
                edtPLAdmin.Text = WorkerInfo2(q9, edtPLAdmin);
                edtPC.Text = WorkerInfo2(q3, edtPC);
                edtEStatus.Text = WorkerInfo2(q4, edtEStatus);
                edtShifts.Text = WorkerInfo2(q11, edtShifts);
                edtHRate.Text = WorkerInfo2(q12, edtHRate);
            }
        }

        private void button21_Click(object sender, EventArgs e)
        {
            pbxGreen2.Visible = false;
            cbxEAdmin.Items.Clear();
            edtNAdmin.Text = "";
            edtSAdmin.Text = "";
            edtTAdmin.Text = "";
            edtMAdmin.Text = "";
            edtFAdmin.Text = "";
            edtFRAdmin.Text = "";
            edtSLAdmin.Text = "";
            edtPLAdmin.Text = "";
            edtPC.Text = "";
            edtEStatus.Text = "";
            string querz = "SELECT workerid FROM users ORDER By workerid ASC";
            FillDropDownList(querz, cbxEAdmin);
        }
        private void setMaxmin()
        {
            dtmFrom.MinDate = DateTime.ParseExact(firstupdate, "dd/MM/yyyy", null);
            dtmFrom.MaxDate = DateTime.ParseExact(lastupdate, "dd/MM/yyyy", null);
            dtmTo.MinDate = DateTime.ParseExact(firstupdate, "dd/MM/yyyy", null);
            dtmTo.MaxDate = DateTime.ParseExact(lastupdate, "dd/MM/yyyy", null);
        }

        public IEnumerable<DateTime> EachDay(DateTime from, DateTime thru)
        {
            for (var day = from.Date; day.Date <= thru.Date; day = day.AddDays(1))
                yield return day;
        }

        private void button23_Click(object sender, EventArgs e)
        {
            int hmany = cbxx.CheckedItems.Count;
            int[] cbx = new int[8];
            int count = 0;

            foreach (int indexChecked in cbxx.CheckedIndices)
            {
                count++;
                cbx[count - 1] = indexChecked;
            }
            grR.Columns.Clear();
            /*0*/
            bool bAttended = false;
            /*1*/
            bool bUL = false;
            /*2*/
            bool bPL = false;
            /*3*/
            bool bPFR = false;
            /*4*/
            bool bUSC = false;
            /*5*/
            bool bAW = false;
            /*6*/
            bool bPSC = false;
            /*7*/
            bool bIOD = false;
            for (int x = 0; x <= hmany - 1; x++)
            {
                int z = cbx[x];
                switch (z)
                {
                    case 0: bAttended = true;
                        break;
                    case 1: bUL = true;
                        break;
                    case 2: bPL = true;
                        break;
                    case 3: bPFR = true;
                        break;
                    case 4: bUSC = true;
                        break;
                    case 5: bAW = true;
                        break;
                    case 6: bPSC = true;
                        break;
                    case 7: bIOD = true;
                        break;
                    default: break;
                }
            }

            DateTime fromd = dtmFrom.Value;
            DateTime tod = dtmTo.Value;
            string query = "";
            string worker = cbxReport.Text;
            string dayz = "";
            string item = "";
            int attended = 0;
            int psc = 0;
            int ul = 0;
            int pl = 0;
            int pfr = 0;
            int usc = 0;
            int aw = 0;
            int iod = 0;
            int shifts = 0;
            try
            {
                SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
                foreach (DateTime day in EachDay(fromd, tod))
                {//start foreach
                    try
                    {
                        dayz = day.ToString("dd/MM/yyyy");
                        query = "SELECT yearly.'" + dayz + "' FROM yearly WHERE workerid = '" + worker + "'";
                        string query2 = "SELECT totalshifts FROM users WHERE workerid = '" + worker + "'";
                        item = GetItem(query);
                        shifts = GetShifts(query2);
                        if (bAttended == true)
                        {
                            switch (item)
                            {
                                case "ATTENDED": attended += 1; break;
                                default: break;
                            }
                        }
                        if (bUL == true)
                        {
                            switch (item)
                            {
                                case "UL": ul += 1; break;
                                default: break;
                            }
                        }
                        if (bPSC == true)
                        {
                            switch (item)
                            {
                                case "PSC": psc += 1; break;
                                default: break;
                            }
                        }
                        if (bPL == true)
                        {
                            switch (item)
                            {
                                case "PL": pl += 1; break;
                                default: break;
                            }
                        }
                        if (bPFR == true)
                        {
                            switch (item)
                            {
                                case "PFR": pfr += 1; break;
                                default: break;
                            }
                        }
                        if (bUSC == true)
                        {
                            switch (item)
                            {
                                case "USC": usc += 1; break;
                                default: break;
                            }
                        }
                        if (bAW == true)
                        {
                            switch (item)
                            {
                                case "AW": aw += 1; break;
                                default: break;
                            }
                        }
                        if (bIOD == true)
                        {
                            switch (item)
                            {
                                case "IOD": iod += 1; break;
                                default: break;
                            }
                        }
                    }
                    catch { }
                }//end foreach loop

            }//end try
            catch (Exception ex)
            {
                MessageBox.Show("No data exists for those dates: {0}", ex.ToString());
            }//end catch
            int colcount = 0;
            double hours = getHours(worker);
            grR.Rows.Clear();
            grR.Columns.Clear();
            grR.Columns.Insert(colcount, new DataGridViewColumn() { HeaderText = "Employee Number", CellTemplate = new DataGridViewTextBoxCell() });
            grR.Rows[0].Cells[colcount].Value = cbxReport.Text;
            colcount++;
            // grR.Columns.Add(new DataGridViewColumn() { HeaderText = "EmployeeNumber", CellTemplate = new DataGridViewTextBoxCell() });
            // int indexlol = grR.Columns["EmployeeNumber"].DisplayIndex;
            // grR.Rows[0].Cells[grR.Columns.IndexOf(grR.Columns["EmployeeNumber"])].Value = cbxReport.Text;
            if (bAttended == true)
            {
                grR.Columns.Insert(colcount, new DataGridViewColumn() { HeaderText = "Total Attended", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Rows[0].Cells[colcount].Value = attended.ToString();
                colcount++;
            }
            if (bUL == true)
            {
                grR.Columns.Insert(colcount, new DataGridViewColumn() { HeaderText = "Total Unpaid Leave", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Rows[0].Cells[colcount].Value = ul.ToString();
                colcount++;
            }
            if (bPL == true)
            {
                grR.Columns.Insert(colcount, new DataGridViewColumn() { HeaderText = "Total Paid Leave", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Rows[0].Cells[colcount].Value = pl.ToString();
                colcount++;
            }
            if (bPFR == true)
            {
                grR.Columns.Insert(colcount, new DataGridViewColumn() { HeaderText = "Total PFR", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Rows[0].Cells[colcount].Value = pfr.ToString();
                colcount++;
            }
            if (bUSC == true)
            {
                grR.Columns.Insert(colcount, new DataGridViewColumn() { HeaderText = "Total USC", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Rows[0].Cells[colcount].Value = usc.ToString();
                colcount++;
            }
            if (bAW == true)
            {
                grR.Columns.Insert(colcount, new DataGridViewColumn() { HeaderText = "Total Awol", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Rows[0].Cells[colcount].Value = aw.ToString();
                colcount++;

            }
            if (bPSC == true)
            {
                grR.Columns.Insert(colcount, new DataGridViewColumn() { HeaderText = "Total PSC", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Rows[0].Cells[colcount].Value = psc.ToString();
                colcount++;
            }
            if (bIOD == true)
            {
                grR.Columns.Insert(colcount, new DataGridViewColumn() { HeaderText = "Total IOD", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Rows[0].Cells[colcount].Value = iod.ToString();
                colcount++;
            }
            grR.Columns.Insert(colcount, new DataGridViewColumn() { HeaderText = "Total Shifts", CellTemplate = new DataGridViewTextBoxCell() });
            grR.Rows[0].Cells[colcount].Value = shifts.ToString();
            colcount++;
            string q1 = "SELECT hourlyrate FROM users WHERE workerid = '" + worker + "'";
            double rate = GetRate(q1);
            grR.Columns.Insert(colcount, new DataGridViewColumn() { HeaderText = "Total Money Owed", CellTemplate = new DataGridViewTextBoxCell() });
            grR.Rows[0].Cells[colcount].Value = "R" + (hours * rate).ToString();
            colcount++;

            /*   grR.Columns.Insert(colcount, new DataGridViewColumn() { HeaderText = "Total Shifts", CellTemplate = new DataGridViewTextBoxCell() });
               grR.Rows[0].Cells[colcount].Value = shifts.ToString();
               colcount++; */

        }//end button click event handler

        private void textBox3_TextChanged(object sender, EventArgs e)
        {

        }

        private void textBox7_TextChanged(object sender, EventArgs e)
        {

        }

        private void textBox8_TextChanged(object sender, EventArgs e)
        {

        }

        private void textBox6_TextChanged(object sender, EventArgs e)
        {

        }

        private void tabPage1_Click(object sender, EventArgs e)
        {

        }

        private void button15_Click(object sender, EventArgs e)
        {
            tabControl1.SelectedTab = tabTerm;
        }
        /* RETURN COUNT VALUES FOR ALL WORKERS */
        private void returnAtt(ref int att, SQLiteConnection con, string column)
        {
            int a = att;
            //    string query = "SELECT Count(*) FROM yearly WHERE yearly.02//12//13 = ATTENDED";        
            string query = "SELECT 04/12/2013 FROM yearly WHERE 04/12/2013 = PSC";
            con.Open();
            SQLiteCommand cmd = new SQLiteCommand(con);
            cmd.CommandText = query;
            //   cmd.Parameters.Add(new SQLiteParameter("@p1", column)); 1
            a += (int)cmd.ExecuteScalar();
            att = a;
        }

        private void returnPSC(ref int psc, SQLiteConnection con, string column)
        {
            int a = psc;
            string query = "SELECT Count(yearly.'" + column + "') FROM yearly WHERE yearly.'" + column + "' = PSC";
            con.Open();
            SQLiteCommand cmd = new SQLiteCommand(con);
            cmd.CommandText = query;
            a += (int)cmd.ExecuteScalar();
            psc = a;
        }

        private void returnUL(ref int ul, SQLiteConnection con, string column)
        {
            int a = ul;
            string query = "SELECT Count(yearly.'" + column + "') FROM yearly WHERE yearly.'" + column + "' = UL";
            con.Open();
            SQLiteCommand cmd = new SQLiteCommand(con);
            cmd.CommandText = query;
            a += (int)cmd.ExecuteScalar();
            ul = a;
        }

        private void returnPL(ref int pl, SQLiteConnection con, string column)
        {
            int a = pl;
            string query = "SELECT Count(yearly.'" + column + "') FROM yearly WHERE yearly.'" + column + "' = PL";
            con.Open();
            SQLiteCommand cmd = new SQLiteCommand(con);
            cmd.CommandText = query;
            a += (int)cmd.ExecuteScalar();
            pl = a;
        }

        private void returnPFR(ref int pfr, SQLiteConnection con, string column)
        {
            int a = pfr;
            string query = "SELECT Count(yearly.'" + column + "') FROM yearly WHERE yearly.'" + column + "' = PFR";
            con.Open();
            SQLiteCommand cmd = new SQLiteCommand(con);
            cmd.CommandText = query;
            a += (int)cmd.ExecuteScalar();
            pfr = a;
        }

        private void returnUSC(ref int usc, SQLiteConnection con, string column)
        {
            int a = usc;
            string query = "SELECT Count(yearly.'" + column + "') FROM yearly WHERE yearly.'" + column + "' = USC";
            con.Open();
            SQLiteCommand cmd = new SQLiteCommand(con);
            cmd.CommandText = query;
            a += (int)cmd.ExecuteScalar();
            usc = a;
        }

        private void returnAW(ref int aw, SQLiteConnection con, string column)
        {
            int a = aw;

            string query = "SELECT Count(yearly.'" + column + "') FROM yearly WHERE yearly.'" + column + "' = AW";
            con.Open();
            SQLiteCommand cmd = new SQLiteCommand(con);
            cmd.CommandText = query;
            a += (int)cmd.ExecuteScalar();
            aw = a;
        }

        /*END OF RETURNING ALL VALUES FOR ALL DATA*/



        private void button14_Click(object sender, EventArgs e)
        {
            tabControl1.SelectedTab = tabOne;
        }

        private void button16_Click(object sender, EventArgs e)
        {
            tabControl1.SelectedTab = tabUpdate;
        }

        private void button25_Click(object sender, EventArgs e)
        {
            MessageBox.Show(" ");
        }

        private void dataGridView1_CellContentDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            string wid = grR.CurrentCell.Value.ToString();
            MessageBox.Show(wid);
        }

        private void button26_Click(object sender, EventArgs e)
        {

        }

        private void treeView1_AfterSelect(object sender, TreeViewEventArgs e)
        {

        }

        private void button28_Click(object sender, EventArgs e)
        {
            try
            {
                log = "";
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: {0}", ex.ToString());
            }
        }

        private void cbxReport_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        List<int> psclist = new List<int>();
        List<int> ullist = new List<int>();
        List<int> pllist = new List<int>();
        List<int> pfrlist = new List<int>();
        List<int> usclist = new List<int>();
        List<int> awlist = new List<int>();
        List<int> iodlist = new List<int>();
        List<int> ablist = new List<int>();
        List<int> matlist = new List<int>();

        

        private void BubbleSort(List<string> workerid, List<int> totalabsent, List<int> psclist1, List<int> ullist1, List<int> pllist1, List<int> pfrlist1, List<int> usclist1, List<int> awlist1, List<int> iodlist1, List<int> ablist1, List<int> matlist1)
        {
            int count = workerid.Count;
            for (int z = 0; z <= count - 1; z++)
            {
                for (int x = 0; x <= count - 2; x++)
                {
                    if (totalabsent[x] < totalabsent[x + 1])
                    {
                        int temp1 = totalabsent[x + 1];
                        string temp2 = workerid[x + 1];
                        int psctemp = psclist1[x + 1];
                        int ultemp = ullist1[x + 1];
                        int pltemp = pllist1[x + 1];
                        int pfrtemp = pfrlist1[x + 1];
                        int usctemp = usclist1[x + 1];
                        int awtemp = awlist1[x + 1];
                        int iodtemp = iodlist1[x + 1];
                        int abtemp = ablist1[x + 1];
                        int mattemp = matlist1[x + 1];

                        totalabsent[x + 1] = totalabsent[x];
                        totalabsent[x] = temp1;

                        workerid[x + 1] = workerid[x];
                        workerid[x] = temp2;

                        psclist1[x + 1] = psclist1[x];
                        psclist1[x] = psctemp;

                        ullist1[x + 1] = ullist1[x];
                        ullist1[x] = ultemp;
                        
                        pllist1[x + 1] = pllist1[x];
                        pllist1[x] = pltemp;

                        pfrlist1[x + 1] = pfrlist1[x];
                        pfrlist1[x] = pfrtemp;

                        usclist1[x + 1] = usclist1[x];
                        usclist1[x] = usctemp;

                        awlist1[x + 1] = awlist1[x];
                        awlist1[x] = awtemp;

                        iodlist1[x + 1] = iodlist1[x];
                        iodlist1[x] = iodtemp;

                        ablist1[x + 1] = ablist1[x];
                        ablist1[x] = abtemp;

                        matlist1[x + 1] = matlist1[x];
                        matlist1[x] = mattemp;

                    }
                }
            }
        }

        private int GetItemRep(DateTime from, DateTime too, string workeridd, string query,out int psc2, out int ul2, out int pl2, out int pfr2, out int usc2, out int aw2,out int iod2, out int ab2, out int mat2)
        {
            string retval = "";
            SQLiteDataReader dr;
            SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
            con.Open();
            //      string query = "SELECT * FROM yearly WHERE yearly.workerid='P0035'";
            SQLiteCommand cmd = new SQLiteCommand(query, con);
            SQLiteDataReader myReader = cmd.ExecuteReader();
            string dayz = "";
            int total = 0;
            int count = 0;
            int psc1 = 0;
            int ul1 = 0;
            int pl1 = 0;
            int pfr1 = 0;
            int usc1 = 0;
            int aw1 = 0;
            int iod1 = 0;
            int mat1 = 0;
            int ab1 = 0;
            while (myReader.Read())
            {
                try
                {
                    foreach (DateTime day in EachDay(from, too))
                    {
                        dayz = day.ToString("dd/MM/yyyy");
                        try
                        {
                            retval = myReader[dayz].ToString();
                        }
                        catch { }
                        int test = 0;
                        // or alternatively:
                        // string fname2 = myReader["FirstName"];
                        // Either of these should work
                        //       if ((retval != "ATTENDED") && (retval != ""))
                        //   total++;
                        if ((retval == "PSC") || (retval == "UL") || (retval == "PL") || (retval == "PFR") || (retval == "USC") || (retval == "AW") || (retval == "IOD") || (retval == "AB") || (retval == "MAT"))
                        {
                            total++;
                        }
                        switch (retval)
                        {
                            case "PSC": psc1++;
                                break;
                            case "UL": ul1++;
                                break;
                            case "PL": pl1++;
                                break;
                            case "PFR": pfr1++;
                                break;
                            case "USC": usc1++;
                                break;
                            case "AW": aw1++;
                                break;
                            case "IOD": iod1++;
                                break;
                            case "AB": ab1++;
                                break;
                            case "MAT": mat1++;
                                break;
                        }                        
                    }
                }
                catch { }
            }
            // Run through the results
            myReader.Close();
            con.Close();
            psc2 = psc1;
            ul2 = ul1;
            pl2 = pl1;
            pfr2 = pfr1;
            usc2 = usc1;
            aw2 = aw1;
            iod2 = iod1;
            ab2 = ab1;
            mat2 = mat1;
            return total;
        }
        private void button24_Click(object sender, EventArgs e)
        {
            int psc1 = 0;
            int ul1 = 0;
            int pl1 = 0;
            int pfr1 = 0;
            int usc1 = 0;
            int aw1 = 0;
            int iod1 = 0;
            int mat1 = 0;
            int ab1 = 0;
            try
            {
                string querycbx = "SELECT workerid FROM yearly WHERE yearly.status = 1";
                FillDropDownList(querycbx, cbxReport);
                grR.Columns.Clear();
                grR.Rows.Clear();
                DateTime fromd = dtmFrom.Value;
                DateTime tod = dtmTo.Value;
                string dayz = "";
                string query = "";
                int items = cbxReport.Items.Count;
                string wid = "";
                string item = "";
                List<string> wids = new List<string>();
                List<int> totabsent = new List<int>();
                
                List<int> psclist = new List<int>();
                List<int> ullist = new List<int>();
                List<int> pllist = new List<int>();
                List<int> pfrlist = new List<int>();
                List<int> usclist = new List<int>();
                List<int> awlist = new List<int>();
                List<int> iodlist = new List<int>();
                List<int> ablist = new List<int>();
                List<int> matlist = new List<int>();
                int total = 0;
                SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
                //   string query1 = "SELECT * FROM main.yearly LEFT JOIN main.users ON yearly.workerid = users.workerid AND users.status = 1 WHERE (yearly.workerid = '" + wid + "')";
                //      string query1 = "SELECT * FROM yearly,users WHERE yearly.workerid = users.workerid AND yearly.workerid = '" + wid + "' AND users.status = 1";
                // string query1 = "SELECT * FROM yearly WHERE EXISTS (SELECT workerid, status FROM users WHERE users.status = 1 AND users.workerid = '" + wid + "')";
                string query1 = "";
                for (int z = 0; z <= items - 1; z++)
                {
                    total = 0;
                    psc1 = 0;
                    ul1 = 0;
                    pl1 = 0;
                    pfr1 = 0;
                    usc1 = 0;
                    aw1 = 0;
                    iod1 = 0;
                    mat1 = 0;
                    ab1 = 0;
                    wid = cbxReport.Items[z].ToString();
                    query1 = "SELECT * FROM yearly WHERE workerid = '" + wid + "'";
                    wids.Add(wid);
                    total = GetItemRep(fromd, tod, wid, query1,out psc1,out ul1,out pl1,out pfr1,out usc1,out aw1,out iod1,out ab1,out mat1);
                    totabsent.Add(total);
                    psclist.Add(psc1);
                    ullist.Add(ul1);
                    pllist.Add(pl1);
                    pfrlist.Add(pfr1);
                    usclist.Add(usc1);
                    awlist.Add(aw1);
                    iodlist.Add(iod1);
                    ablist.Add(ab1);
                    matlist.Add(mat1);
                }

                con.Close();
                BubbleSort(wids, totabsent,psclist,ullist,pllist,pfrlist,usclist,awlist,iodlist,ablist,matlist);
                grR.Columns.Insert(0, new DataGridViewColumn() { HeaderText = "Number", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Columns.Insert(1, new DataGridViewColumn() { HeaderText = "Employee Number", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Columns.Insert(2, new DataGridViewColumn() { HeaderText = "Total Days Absent", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Columns.Insert(3, new DataGridViewColumn() { HeaderText = "PSC", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Columns.Insert(4, new DataGridViewColumn() { HeaderText = "UL", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Columns.Insert(5, new DataGridViewColumn() { HeaderText = "PL", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Columns.Insert(6, new DataGridViewColumn() { HeaderText = "PFR", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Columns.Insert(7, new DataGridViewColumn() { HeaderText = "USC", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Columns.Insert(8, new DataGridViewColumn() { HeaderText = "AW", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Columns.Insert(9, new DataGridViewColumn() { HeaderText = "IOD", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Columns.Insert(10, new DataGridViewColumn() { HeaderText = "AB", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Columns.Insert(11, new DataGridViewColumn() { HeaderText = "MAT", CellTemplate = new DataGridViewTextBoxCell() });
                for (int y = 0; y <= 14; y++)
                {
                    //  grR.Rows[y].Cells[0].Value = wids[y];
                    grR.Rows.Add(y + 1, wids[y], totabsent[y].ToString(),psclist[y].ToString(),ullist[y].ToString(),pllist[y].ToString(),pfrlist[y].ToString(),usclist[y].ToString(),awlist[y].ToString(),iodlist[y].ToString(),ablist[y].ToString(),matlist[y].ToString());
                    //   grR.Rows[y].Cells[1].Value = totabsent[y];
                }
            }
            catch { MessageBox.Show("No Records"); }
        }



        private void button26_Click_1(object sender, EventArgs e)
        {
            int psc1 = 0;
            int ul1 = 0;
            int pl1 = 0;
            int pfr1 = 0;
            int usc1 = 0;
            int aw1 = 0;
            int iod1 = 0;
            int mat1 = 0;
            int ab1 = 0;
            try
            {
                string querycbx = "SELECT workerid FROM yearly WHERE yearly.status = 0";
                FillDropDownList(querycbx, cbxReport);
                grR.Columns.Clear();
                grR.Rows.Clear();
                DateTime fromd = dtmFrom.Value;
                DateTime tod = dtmTo.Value;
                string dayz = "";
                string query = "";
                int items = cbxReport.Items.Count;
                string wid = "";
                string item = "";
                List<string> wids = new List<string>();
                List<int> totabsent = new List<int>();

                List<int> psclist = new List<int>();
                List<int> ullist = new List<int>();
                List<int> pllist = new List<int>();
                List<int> pfrlist = new List<int>();
                List<int> usclist = new List<int>();
                List<int> awlist = new List<int>();
                List<int> iodlist = new List<int>();
                List<int> ablist = new List<int>();
                List<int> matlist = new List<int>();
                int total = 0;
                SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
                //   string query1 = "SELECT * FROM main.yearly LEFT JOIN main.users ON yearly.workerid = users.workerid AND users.status = 1 WHERE (yearly.workerid = '" + wid + "')";
                //      string query1 = "SELECT * FROM yearly,users WHERE yearly.workerid = users.workerid AND yearly.workerid = '" + wid + "' AND users.status = 1";
                // string query1 = "SELECT * FROM yearly WHERE EXISTS (SELECT workerid, status FROM users WHERE users.status = 1 AND users.workerid = '" + wid + "')";
                string query1 = "";
                for (int z = 0; z <= items - 1; z++)
                {
                    total = 0;
                    psc1 = 0;
                    ul1 = 0;
                    pl1 = 0;
                    pfr1 = 0;
                    usc1 = 0;
                    aw1 = 0;
                    iod1 = 0;
                    mat1 = 0;
                    ab1 = 0;
                    wid = cbxReport.Items[z].ToString();
                    query1 = "SELECT * FROM yearly WHERE workerid = '" + wid + "'";
                    wids.Add(wid);
                    total = GetItemRep(fromd, tod, wid, query1, out psc1, out ul1, out pl1, out pfr1, out usc1, out aw1, out iod1, out ab1, out mat1);
                    totabsent.Add(total);
                    psclist.Add(psc1);
                    ullist.Add(ul1);
                    pllist.Add(pl1);
                    pfrlist.Add(pfr1);
                    usclist.Add(usc1);
                    awlist.Add(aw1);
                    iodlist.Add(iod1);
                    ablist.Add(ab1);
                    matlist.Add(mat1);
                }

                con.Close();
                BubbleSort(wids, totabsent, psclist, ullist, pllist, pfrlist, usclist, awlist, iodlist, ablist, matlist);
                grR.Columns.Insert(0, new DataGridViewColumn() { HeaderText = "Number", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Columns.Insert(1, new DataGridViewColumn() { HeaderText = "Employee Number", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Columns.Insert(2, new DataGridViewColumn() { HeaderText = "Total Days Absent", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Columns.Insert(3, new DataGridViewColumn() { HeaderText = "PSC", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Columns.Insert(4, new DataGridViewColumn() { HeaderText = "UL", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Columns.Insert(5, new DataGridViewColumn() { HeaderText = "PL", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Columns.Insert(6, new DataGridViewColumn() { HeaderText = "PFR", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Columns.Insert(7, new DataGridViewColumn() { HeaderText = "USC", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Columns.Insert(8, new DataGridViewColumn() { HeaderText = "AW", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Columns.Insert(9, new DataGridViewColumn() { HeaderText = "IOD", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Columns.Insert(10, new DataGridViewColumn() { HeaderText = "AB", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Columns.Insert(11, new DataGridViewColumn() { HeaderText = "MAT", CellTemplate = new DataGridViewTextBoxCell() });
                for (int y = 0; y <= 14; y++)
                {
                    //  grR.Rows[y].Cells[0].Value = wids[y];
                    grR.Rows.Add(y + 1, wids[y], totabsent[y].ToString(), psclist[y].ToString(), ullist[y].ToString(), pllist[y].ToString(), pfrlist[y].ToString(), usclist[y].ToString(), awlist[y].ToString(), iodlist[y].ToString(), ablist[y].ToString(), matlist[y].ToString());
                    //   grR.Rows[y].Cells[1].Value = totabsent[y];
                }
            }
            catch { MessageBox.Show("No Records"); }
        }

        private int CalcIsh(DateTime from, DateTime too, string workeridd, ref int peopleworked, ref int daysworked)
        {
            string retval = "";
            SQLiteDataReader dr;
            SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
            con.Open();
            string query = "SELECT * FROM yearly WHERE yearly.workerid = '" + workeridd + "'";
            SQLiteCommand cmd = new SQLiteCommand(query, con);
            SQLiteDataReader myReader = cmd.ExecuteReader();
            string dayz = "";
            int total = 0;
            int dw = daysworked;
            try
            {
                while (myReader.Read())
                {
                    // MessageBox.Show("Hi");
                    foreach (DateTime day in EachDay(from, too))
                    {
                        dayz = day.ToString("dd/MM/yyyy");
                        try
                        {
                            retval = myReader[dayz].ToString();
                        }
                        catch { }
                        // or alternatively:
                        // string fname2 = myReader["FirstName"];
                        // Either of these should work
                        if ((retval == "PSC") || (retval == "UL") || (retval == "PL") || (retval == "PFR") || (retval == "USC") || (retval == "AW") || (retval == "IOD") || (retval == "AB") || (retval == "MAT"))
                        {
                            total++;
                        }
                        dw++;
                  /*      if ((retval != "") && (retval != "NO WORK"))
                            peopleworked++;*/
                        if ((retval == "ATTENDED") || (retval == "PSC") || (retval == "UL") || (retval == "PL") || (retval == "PFR") || (retval == "USC") || (retval == "AW") || (retval == "IOD") || (retval == "AB") || (retval == "MAT"))
                        {
                            peopleworked++;
                        }
                    }   
                    break;
                }
            }
            catch (Exception ex)
            {
                //   MessageBox.Show(ex.ToString());
            }

            // Run through the results
            myReader.Close();
            con.Close();
            daysworked = dw;
            return total;
        }

        private int CalcIsh1(DateTime from, DateTime too, string workeridd, ref int peopleworked, ref int daysworked)
        {
            string retval = "";
            SQLiteDataReader dr;
            SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");
            con.Open();
            string query = "SELECT * FROM yearly WHERE yearly.workerid = '" + workeridd + "'";
            SQLiteCommand cmd = new SQLiteCommand(query, con);
            SQLiteDataReader myReader = cmd.ExecuteReader();
            string dayz = "";
            int total = 0;
            int dw = daysworked;
            try
            {
                while (myReader.Read())
                {
                    // MessageBox.Show("Hi");
                    foreach (DateTime day in EachDay(from, too))
                    {
                        dayz = day.ToString("dd/MM/yyyy");
                        retval = myReader[dayz].ToString();
                        // or alternatively:
                        // string fname2 = myReader["FirstName"];
                        // Either of these should work
                        if ((retval != "ATTENDED") && (retval != ""))
                            total++;
                        dw++;
                        if ((retval != "") && (retval != "NO WORK"))
                            peopleworked++;
                    }
                    break;
                }
            }
            catch (Exception ex)
            {
                //   MessageBox.Show(ex.ToString());
            }

            // Run through the results
            myReader.Close();
            con.Close();
            daysworked = dw;
            return total;
        }

        private void button27_Click(object sender, EventArgs e)
        {
            try
            {
                string querycbx = "SELECT workerid FROM yearly WHERE yearly.status = 1";
                FillDropDownList(querycbx, cbxReport);
                grR.Columns.Clear();
                grR.Rows.Clear();
                DateTime fromd = dtmFrom.Value;
                DateTime tod = dtmTo.Value;
                string dayz = "";
                string query = "";
                int items = cbxReport.Items.Count;
                string wid = "";
                string item = "";
                List<string> wids = new List<string>();
                List<int> totabsent = new List<int>();
                int total = 0;
                int peoplework = 0;
                int numdaysworked = 0;
                //SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");

                for (int z = 0; z <= items - 1; z++)
                {
                    wid = cbxReport.Items[z].ToString();
                    numdaysworked = 0;
                    total = total + CalcIsh(fromd, tod, wid, ref peoplework, ref numdaysworked);
                }
                double perc = (((total * 1.0) / (peoplework)) * 100);

                // con.Close();
                grR.Columns.Insert(0, new DataGridViewColumn() { HeaderText = "From", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Columns.Insert(1, new DataGridViewColumn() { HeaderText = "To", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Columns.Insert(2, new DataGridViewColumn() { HeaderText = "Percentage", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Rows.Add(fromd.ToString("dd/MM/yyyy"), tod.ToString("dd/MM/yyyy"), perc.ToString() + "%");
            }
            catch { MessageBox.Show("No Data"); }
        }


        private void button29_Click(object sender, EventArgs e)
        {
            try
            {
                string querycbx = "SELECT workerid FROM yearly WHERE yearly.status = 0";
                FillDropDownList(querycbx, cbxReport);
                grR.Columns.Clear();
                grR.Rows.Clear();
                DateTime fromd = dtmFrom.Value;
                DateTime tod = dtmTo.Value;
                string dayz = "";
                string query = "";
                int items = cbxReport.Items.Count;
                string wid = "";
                string item = "";
                List<string> wids = new List<string>();
                List<int> totabsent = new List<int>();
                int total = 0;
                int peoplework = 0;
                int numdaysworked = 0;
                //SQLiteConnection con = new SQLiteConnection(@"Data Source=crh-africa.sqlite;Version=3;");

                for (int z = 0; z <= items - 1; z++)
                {
                    wid = cbxReport.Items[z].ToString();
                    numdaysworked = 0;
                    total = total + CalcIsh(fromd, tod, wid, ref peoplework, ref numdaysworked);
                }
                double perc = (((total * 1.0) / (peoplework)) * 100);

                // con.Close();
                grR.Columns.Insert(0, new DataGridViewColumn() { HeaderText = "From", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Columns.Insert(1, new DataGridViewColumn() { HeaderText = "To", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Columns.Insert(2, new DataGridViewColumn() { HeaderText = "Percentage", CellTemplate = new DataGridViewTextBoxCell() });
                grR.Rows.Add(fromd.ToString("dd/MM/yyyy"), tod.ToString("dd/MM/yyyy"), perc.ToString() + "%");
            }
            catch { MessageBox.Show("No data"); }
        }

        private void button30_Click(object sender, EventArgs e)
        {
            string query = "SELECT workerid FROM yearly";
            FillDropDownList(query, cbxReport);
        }

        private void tabAdmin_Click(object sender, EventArgs e)
        {

        }

        private void button25_Click_1(object sender, EventArgs e)
        {
           
        }

        private void optionsToolStripMenuItem_Click(object sender, EventArgs e)
        {

        }

        private void updateListToolStripMenuItem_Click(object sender, EventArgs e)
        {
            tabControl1.SelectedTab = tabUpdate;
        }

        private void btnlol_Click(object sender, EventArgs e)
        {
            /* string filename = saveFileDialog1.FileName;
             saveFileDialog1.FileName = FileName;*/
            if (saveFileDialog2.ShowDialog(this) == DialogResult.OK)
                WriteFile1(saveFileDialog2.FileName + ".csv");
        }

    }
}
